package persistence.combo.comboDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;

import dbExceptions.DataBaseErrorException;
import article.exceptions.BadArticleException;
import article.exceptions.BadCategoryException;
import article.exceptions.MissingArticleException;
import article.exceptions.MissingCategoryException;
import business.article.entities.Article;
import business.article.entities.Category;
import business.article.entities.Combo;
import business.article.entities.Ingredient;
import persistence.DAO.DAOHelper;

public class ComboDAO {
	/**
	 * Singleton implementation begin
	 */
	
	private static ComboDAO instance = null;

	private ComboDAO() {
	}

	public static ComboDAO getInstance() {
		if (instance == null)
			instance = new ComboDAO();
		return instance;
	}

	/**
	 * Singleton implementation end
	 * 
	 * @throws badClassException
	 */
	
	public Combo[] getCombos(){
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery(
				  "SELECT name,description,primary_key,price,inner_key FROM Article WHERE active=1 AND id_category=0");
				  ArrayList<Combo> articulos=new ArrayList<Combo>();
				  
				  while(r.next()){
					  articulos.add(new Combo(r.getString(1), r.getString(2), r.getInt(3), r.getDouble(4), getArticles(r.getInt(5)))); }
				  
				  Combo[] ingr=new Combo[articulos.size()];
				  articulos.toArray(ingr); return ingr; } catch (SQLException e) { e.printStackTrace();
				  }finally{
						if(con!=null)
							try {
								con.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}	
				  }
				  return null;
	}
	
	private ArrayList<Article> getArticles(int combo_key) {
		Connection con=null;
		try { 
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery(
				  "SELECT name,description,primary_key,price FROM Article JOIN Article_Combo ON Article.inner_key=Article_Combo.id_atomizable_unit WHERE active=1 AND Article_Combo.id_combo="+String.valueOf(combo_key));
				  ArrayList<Article> articulos=new ArrayList<Article>();
				  
				  while(r.next()){articulos.add(new Article(r.getString(1),
				  r.getString(2), r.getInt(3), r.getDouble(4),
				  getIngredients(r.getInt(3)),
				  getCategory(r.getInt(3)))); }
				  
				  return articulos; } catch (SQLException e) { e.printStackTrace();
				  }finally{
						if(con!=null)
							try {
								con.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}	
				  }
				  
				  return null;
				 
	}

	private Category getCategory(int articleID) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat = con.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT Category.name,Category.primary_key FROM Category JOIN Article ON Article.id_category=Category.inner_key WHERE Article.primary_key="
							+ String.valueOf(articleID));
			rs.next();
			Category c = new Category(rs.getString(1), rs.getInt(2));
			stat.close();
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	private Ingredient[] getIngredients(int articleID) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat = con.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT Ingredient.name,Ingredient.in_stock,Ingredient.inner_key FROM Ingredient " +
							"JOIN Article_Ingredient ON Article_Ingredient.id_ingredient=Ingredient.inner_key " +
							"JOIN Atomizable_unit ON Atomizable_unit.inner_key=Article_Ingredient.id_atomizable_unit " +
							"JOIN Article ON Article.inner_key=Atomizable_unit.inner_key" +
							" WHERE Article.primary_key="
							+ String.valueOf(articleID));
			ArrayList<Ingredient> ingredientes = new ArrayList<Ingredient>();
			while (rs.next()) {
				ingredientes.add(new Ingredient(rs.getString(1), rs.getBoolean(2), rs.getInt(3)));
			}
			stat.close();
			Ingredient[] ingr = new Ingredient[ingredientes.size()];
			ingredientes.toArray(ingr);
			return ingr;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

		return null;
	}

	public void newCombo(Combo comb)  throws MissingCategoryException, BadCategoryException {
		if (comb.getCat()==null) throw new MissingCategoryException("MissingCategoryException");
		if (comb.getCat().getPrimaryKey()!=0){ 
			throw new BadCategoryException();
		}
		
		Connection con=null;
		try {
		con=DAOHelper.getInstance().getConnection();
		Statement st=con.createStatement();
		Integer articulo_id = getNewID();
		st.execute("INSERT INTO Article (name,price,id_category,primary_key,description) VALUES('"
				+ comb.getName()
				+ "',"
				+ String.valueOf(comb.getPrice())
				+ ","
				+ String.valueOf(0)
				+ ","
				+ String.valueOf(articulo_id)
				+ ",'"
				+ comb.getDesc() + "')");
		
		Statement st2=con.createStatement();
		ResultSet s = st2.executeQuery("SELECT inner_key FROM Article WHERE primary_key="+ String.valueOf(articulo_id));
		s.next();
		int inner_id_articulo = s.getInt(1);
		s.close();
		Statement st3=con.createStatement();
		st3.execute("INSERT INTO Combo (inner_key) VALUES("+inner_id_articulo+")");
		
		for (Article i : comb.getArts()) {
			Statement st4=con.createStatement();
			
			ResultSet rs=st4.executeQuery("SELECT inner_key FROM Article WHERE primary_key="+String.valueOf(i.getPrimaryKey()));
			rs.next();
			String sql="INSERT INTO Article_Combo " +
					"(id_atomizable_unit,id_combo) VALUES("
					+ String.valueOf(rs.getInt(1))
					+ ","
					+ String.valueOf(inner_id_articulo) + ")";
			st4.execute(sql);
			rs.close();
		}
		
		}catch(Exception e){
			
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		}
	
	private Integer getNewID() {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r = st.executeQuery("SELECT primary_key FROM Article WHERE id_category="
							+ String.valueOf(0)+ " ORDER BY inner_key ASC");

			ArrayList<Integer> articulos = new ArrayList<Integer>();
			while (r.next()) {
				articulos.add(r.getInt(1));
			}
			
			
			
			if (articulos.size()==0)
				return 0;
			Collections.sort(articulos);
			
		
			
			int i = 0;
			for (Integer j : articulos) {
				
				if (i != j)
					break;
				i++;
			}
			return i;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return null;
	}

	public void deleteCombo(Combo artDel) throws MissingArticleException, BadArticleException, DataBaseErrorException{
		if (artDel==null)
			throw new MissingArticleException("MissingArticleException");
		if(!articleExists(artDel)){
			throw new BadArticleException();
		}
		String sql = "UPDATE Article SET ";
		sql = sql + " active=0";
		sql = sql + ", primary_key=-1";
		sql = sql + ", id_category=-1";
		sql = sql + " WHERE primary_key="
			+ String.valueOf(artDel.getPrimaryKey());
		
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			st.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
	}
	
	private boolean articleExists(Combo c) throws DataBaseErrorException{
		Connection con=null;
		try {
			con =DAOHelper.getInstance().getConnection();
			Statement st=con.createStatement();
			ResultSet r=st.executeQuery("SELECT COUNT(primary_key) FROM Article WHERE primary_key="+String.valueOf(c.getPrimaryKey()));
			r.next();
			int a=r.getInt(1);
			if (a==0) return false;
			if (a==1) return true;
			if (a>1) throw new DataBaseErrorException();  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }
		return false;
		
		
	}
	
	public void updateCombo(Combo comb) throws    BadCategoryException, MissingCategoryException, DataBaseErrorException{
		if (comb==null)
		if (comb.getCat()==null) throw new MissingCategoryException("MissingCategoryException");
		if (comb.getCat().getPrimaryKey()!=0){
			throw new BadCategoryException();
		}
		if(!articleExists(comb)){
		}
		
		String sql = "UPDATE Article SET ";
		sql = sql + "name='" + comb.getName() + "'";
		if (comb.getDesc() == null) {
			sql = sql + ", description=" + "null";
		} else {
			sql = sql + ", description=" + "'" + comb.getDesc() + "'";
		}
		sql = sql + ", price=" + String.valueOf(comb.getPrice());
	
		sql = sql + " WHERE primary_key="
				+ String.valueOf(comb.getPrimaryKey());
		Connection con=null;
		try {
		con=DAOHelper.getInstance().getConnection();
		Statement st=con.createStatement();
			st.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	  }

	}
}
